<%@ page import="java.sql.Statement" import="java.sql.ResultSet" import="java.sql.Connection" %> 
<%@page import="connection.DBConnection"%>
<%@ page import="java.util.Vector" %>
<%@ page import="java.text.DecimalFormat" %>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Trial Balance</title>

<script type="text/javascript" src="http://code.jquery.com/jquery-1.10.2.js"></script>
<script type="text/javascript" src="http://code.jquery.com/ui/1.11.2/jquery-ui.js"></script>
<link href="globalCSS.css" rel="stylesheet" type="text/css"/>
<link rel="stylesheet" href="//code.jquery.com/ui/1.11.2/themes/smoothness/jquery-ui.css">


<style>
	.hidden 
	{
		display : none
	}
</style>
</head>

<body>
<h2 align="center">Trial Balance Report</h2>
<form name="fr2" method="get" action="TrialBalanceServlet3">

  <%
        String exportToExcel = request.getParameter("exportToExcel");
        if (exportToExcel != null && exportToExcel.toString().equalsIgnoreCase("YES")) 
        {
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "inline; filename=" + "TrialBalanceReport.xls");
 
        }
    %>

<div>
<table align="center" border=5 > 

<%  		DBConnection dbc=new DBConnection();
			Connection con=dbc.getNewConnection();
			Statement st = null;
			ResultSet rs = null;
			
			int count=1;
			double debtors=0, creditors=0, cashbank=0, fixedassets=0, other=0, capital=0, otherexpenses=0, sellingandmarketing=0, salaryandwages=0, administrativecharges=0, provision=0, unsecuredloans=0, securedloans=0, investments=0, closingstock=0, openingstock=0, loansandadvances=0, indirectincome=0, bankchargesandinterest=0, depreciation=0; //trial balance
			
			double voucher=0.0,v3=0.0;
			
			String v5="",b5="",d="",d1="",d2="",d3="",min="",max="";
			%>
			<%
			try
			{
				  st=con.createStatement() ;
				  rs=st.executeQuery("SELECT MIN(DATE) FROM ( (SELECT date as DATE FROM salesvoucher2) UNION ALL (SELECT  date as DATE FROM CreditNote) UNION ALL (SELECT date as DATE FROM purchasevoucher2) UNION ALL (SELECT date as DATE FROM DebitNote) UNION ALL (SELECT date as DATE FROM receiptsvoucher2) UNION ALL (SELECT date as DATE FROM paymentvoucher2) UNION ALL (SELECT date as DATE FROM journalvoucher2) ) p");
			
			while(rs.next()){
			
						 			
						        min=rs.getString(1);
			 } 
   		  	
     		  } 
          catch(Exception e) {}//code	        
						     %>
						     
						     
						     <%
			try
			{
				  st=con.createStatement() ;
				  rs=st.executeQuery("SELECT MAX(DATE) FROM ( (SELECT date as DATE FROM salesvoucher2) UNION ALL (SELECT  date as DATE FROM CreditNote) UNION ALL (SELECT date as DATE FROM purchasevoucher2) UNION ALL (SELECT date as DATE FROM DebitNote) UNION ALL (SELECT date as DATE FROM receiptsvoucher2) UNION ALL (SELECT date as DATE FROM paymentvoucher2) UNION ALL (SELECT date as DATE FROM journalvoucher2) ) p");
			
			while(rs.next()){
			
						 			
						        max=rs.getString(1);
			 } 
   		  	
     		  } 
          catch(Exception e) {}	        
						     %>
						     
<tr>
    <td colspan=5> &nbsp &nbsp From:<%=min %> <br>  
			       &nbsp &nbsp To:<%=max %>
</tr>
<tbody>
<tr>
		<th> Sr. Number </th>
		<th> Account Name </th>
		<th> Group </th>
 		<th> Transaction Balance </th>
 		<th> Opening Balance </th>
 		<th> Closing Balance </th>
</tr>


		
			<%
			double totalob=0.0,totalclosing=0.0;
			double openingbalance=0.0,closingbalance=0.0;
			try
			{
				
			   st=con.createStatement() ;
       		  // rs=st.executeQuery("select S.innum,S.scname,S.scode,S.samount,R.renum,R.raccname,R.ramount,R.raccname2,R.ramount2,R.accid,R.racc2id,PY.pyenum,PY.pyaccname,PY,pyamount,PY.pyaccname2,PY.pyamount2,PY.pyaccid,PY.pyacc2id,J.jname,J.jaccdeb,J.jamount1,J.jacccred,J.jamount2,J.jaccid,J.jacc2id from SalesVoucher S, ReceiptsVoucher2 R, PaymentVoucher2 PY, JournalVoucher2 J group by(S.sccode)");
       		  
       		    // rs=st.executeQuery("select s.scname,Sum(S.samount) From SalesVoucher FULL OUTER JOIN ReceiptsVoucher2 ON S.sccode=R.raccid;"); 
 
       		   
       		    rs=st.executeQuery("SELECT code, name, Date, sum(amount), groupname, ob FROM ( (SELECT ccode AS code, scname AS name, date as Date, stota AS amount, sgroupname AS groupname, openingbalance AS ob FROM salesvoucher2) UNION ALL (SELECT ccode AS code, cdcname AS name, date as Date, -(cdtotal) AS amount, cdgroupname AS groupname, openingbalance AS ob FROM CreditNote) UNION ALL (SELECT scode AS code, pvsname AS name, date as Date, -(pvtota) AS amount, pgroupname AS groupname, openingbalance AS ob FROM purchasevoucher2) UNION ALL (SELECT scode AS code, dbsname AS name, date as Date, dbtotal AS amount, dbgroupname AS groupname, openingbalance AS ob FROM DebitNote) UNION ALL (SELECT raccid AS code, raccname AS name, date as Date, -(ramount) AS amount, rgroupname AS groupname, openingbalance AS ob FROM receiptsvoucher2) UNION ALL (SELECT pyaccid AS code, pyaccname AS name, date as Date, pyamount AS amount,pygroupname AS groupname, openingbalance AS ob FROM paymentvoucher2)  UNION ALL (SELECT jaccid AS code, jaccdeb AS name, date as Date, jamount1 AS amount, jgroupname AS groupname, openingbalance1 AS ob FROM journalvoucher2) UNION ALL (SELECT jacc2id AS code, jacccred AS name, date as Date, -(jamount2) AS amount,jgroupname2 AS groupname, openingbalance2 AS ob FROM journalvoucher2) ) p GROUP BY code");
       		   
       		   %>
	        
       		  <%  while(rs.next()){ %>
<tr>
			  <%			
			        String v1=rs.getString(1); //code	        
			        String v2=rs.getString(2); //name 
			        v5=rs.getString(3); //date
					v3=rs.getDouble(4); //amount
					String v4=rs.getString(5); //group
					openingbalance=rs.getDouble(6); //opening balance
					
					totalob=totalob+openingbalance;	
					
					closingbalance=v3+openingbalance;
					totalclosing=totalclosing+closingbalance;
						
				%>
					<td> <%=count%> </td> <%count++; %>
					<td> <a href="http://localhost:8081/BOA_Web/TrialBalanceServlet2?code=<%=v1%>&name=<%=v2%>&group=<%=v4%>"> <%=v2%> </a></td>	
					<td> <%=v4%> </td>
					<td align="right"> <%=v3%> </td>	
					<td align="right"> <%=openingbalance %> </td>	
					<td align="right"><%=closingbalance %> </td>									
<%			  	
					voucher=voucher+v3;
            	 } 
       		  	
       		  } 
            catch(Exception e) {} %>            
</tr>
	
	<%		 double bank=0.0,b4=0.0;
	     
			try
			{
			   st=con.createStatement() ; 
       		   
       		    rs=st.executeQuery("SELECT  bankname, Date, sum(amount11), ob FROM ( (SELECT r.bname AS bankname, r.date as Date, r.ramount AS amount11, b.bopen as ob FROM ReceiptsVoucher2 r,bankmaster b) UNION ALL (SELECT p.bname AS bankname, p.date as Date, -(p.pyamount) AS amount11, b.bopen as ob FROM PaymentVoucher2 p,bankmaster b) ) p GROUP BY bankname");
       		   	//rs=st.executeQuery("select r.bname, sum(r.ramount), sum(r.ramount2), p.bname, sum(p.pyamount), sum(p.pyamount) from receiptsvoucher2 r, paymentvoucher2 p where r.bname=p.bname group by bname");
       		   %>
	        
       		  <%  while(rs.next()){ %>
<tr>
			  <%			
			        String b1=rs.getString(1); //bankname
	 								        
			        
			         b5=rs.getString(2); //date
					double b2=rs.getDouble(3); //amount11
					double bankopeningbalance=rs.getDouble(4); //opening balance
					
					totalob=totalob+bankopeningbalance;
					
					b4=b2; //+b3;
					
					closingbalance=b4+bankopeningbalance;
					totalclosing=totalclosing+closingbalance;
					
				%>			
					<td> <%=count%> </td> <%count++; %>					
					<td> <a href="http://localhost:8081/BOA_Web/TrialBalanceServlet?bn=<%=b1%>"> <%=b1%> </a> </td>	
					<td> Cash and Bank Balance </td>
					<td align="right"> <%=b4%> </td>
					<td align="right"> <%=bankopeningbalance %> </td>	
					<td align="right"><%=closingbalance %> </td>
					
						
				<% bank=bank+b4;								
					}
       		  } 
            catch(Exception e) {} %>     

 		<%
 			double amount1=0.0,amount2=0.0,amount3=0.0;
 			ResultSet rs_sales = null;
 		
 		try
 		{
 			
 			st=con.createStatement() ;
 			rs_sales=st.executeQuery("select SUM(-samount),date,SUM(-stamount),SUM(-strans) from SalesVoucher2");
 			while(rs_sales.next())
 			{
 				amount1=rs_sales.getDouble(1);
 				//amount1=-amount1;
 				
 				 d=rs_sales.getString(2);
 				 
 				amount2=rs_sales.getDouble(3);
 				//amount2=-amount2;
 				
 				amount3=rs_sales.getDouble(4);
 				//amount3=-amount3;				
 			} 			
 		}
 		catch(Exception e) {}
 		%>       
 		
		<%
 			double cdamount1=0.0,cdamount2=0.0,cdamount3=0.0;
			ResultSet rs_credit = null;
		
 		try
 		{
 			st=con.createStatement() ;
 			rs_credit=st.executeQuery("select SUM(cdnet),date,SUM(cdtax),SUM(cdtrans) from CreditNote");
 			while(rs_credit.next())
 			{
 				cdamount1=rs_credit.getDouble(1);
 				 d1=rs_credit.getString(2);
 				cdamount2=rs_credit.getDouble(3);
 				cdamount3=rs_credit.getDouble(4);
 			} 			
 		}
 		catch(Exception e) {}
 		%>  
 		
 		 <%
 			double amount4=0.0,amount5=0.0,amount6=0.0,amount7=0.0;
 			
 		try
 		{
 			st=con.createStatement() ;
 			rs=st.executeQuery("select SUM(pvneta),date,SUM(pvtaxa),SUM(pvtrans),SUM(pvot) from PurchaseVoucher2");
 			while(rs.next())
 			{
 				amount4=rs.getDouble(1);
 				 d2=rs.getString(2);
 				amount5=rs.getDouble(3);
 				amount6=rs.getDouble(4);
 				amount7=rs.getDouble(5);				
 			} 			
 		}
 		catch(Exception e) {}
 		%>     
 		
 		
 		<%
 			double dbamount1=0.0,dbamount2=0.0,dbamount3=0.0,dbamount4=0.0;
 			
 		try
 		{
 			st=con.createStatement() ;
 			rs=st.executeQuery("select SUM(dbnet),date,SUM(dbtax),SUM(dbtrans),SUM(dbot) from DebitNote");
 			while(rs.next())
 			{
 				dbamount1=rs.getDouble(1);
 				 d3=rs.getString(2);
 				dbamount2=rs.getDouble(3);
 				dbamount3=rs.getDouble(4);
 				dbamount4=rs.getDouble(5);				
 			} 			
 		}
 		catch(Exception e) {}
 		%>     

<% 	if(!rs_sales.next() || !rs_credit.next())
	{
%>
	
<tr id="salesnet">
	<td> <%=count %> <% count++; %> </td>
	<td><a href="http://localhost:8081/BOA_Web/SalesNetAmount.jsp?"> Sales Net Amount </a></td>
	<td> Sales </td>
	
	<td align="right"><%=amount1+cdamount1 %></td>
	<td> </td>
	<td align="right"><%=amount1+cdamount1 %></td>
</tr>


<tr>
	<td> <%=count %> <% count++; %> </td>
	<td><a href="http://localhost:8081/BOA_Web/SalesTaxAmount.jsp?"> Sales Tax Amount </a></td>
	<td> Duties And Taxes </td>
	<td align="right"><%=amount2+cdamount2 %></td>
	<td> </td>
	<td align="right"><%=amount2+cdamount2 %></td>
</tr>


<tr>
	<td> <%=count %> <% count++; %> </td>
	<td><a href="http://localhost:8081/BOA_Web/SalesTransportAmount.jsp?"> Sales Transport Amount</a></td>
	<td> Transport Charges </td>
	<td align="right"><%=amount3+cdamount3 %></td>
	<td> </td>
	<td align="right"><%=amount3+cdamount3 %></td>
</tr>

<% } %>

<tr>
	<td> <%=count %> <% count++; %> </td>
	<td> <a href="http://localhost:8081/BOA_Web/PurchaseNetAmount.jsp?">Purchase Net Amount</a></td>
	<td> Purchase </td>
	<td align="right"><%=amount4-dbamount1 %></td>
	<td> </td>
	<td align="right"><%=amount4-dbamount1 %></td>

</tr>


<tr>
	<td> <%=count %> <% count++; %> </td>
	<td><a href="http://localhost:8081/BOA_Web/PurchaseTaxAmount.jsp?">Purchase Tax Amount</a></td>
	<td> Duties And Taxes </td>
	<td align="right"><%=amount5-dbamount2 %></td>
	<td> </td>
	<td align="right"><%=amount5-dbamount2 %></td>
</tr>
	
	
<tr>
	<td> <%=count %> <% count++; %>
	<td><a href="http://localhost:8081/BOA_Web/PurchaseTransportAmount.jsp?"> Purchase Transport Amount</a></td>
	<td> Transport Charges </td>
	<td align="right"><%=amount6-dbamount3 %></td>
	<td> </td>
	<td align="right"><%=amount6-dbamount3 %></td>
</tr>
	
	
<tr>
	<td> <%=count %> <% count++; %>
	<td><a href="http://localhost:8081/BOA_Web/PurchaseOtherCosts.jsp?"> Purchase Other Costs</a></td>
	<td> Other Costs </td>
	<td align="right"><%=amount7-dbamount4 %></td>
	<td> </td>
	<td align="right"><%=amount7-dbamount4 %></td>
</tr>

	<% double sptotal=amount1+amount2+amount3+ amount4+amount5+amount6+amount7 -dbamount1-dbamount2-dbamount3-dbamount4+ cdamount1+cdamount2+cdamount3; 
	
	   double total=sptotal+bank+voucher;
	   
	   totalclosing=totalclosing+sptotal;
	   
	  // DecimalFormat df = new DecimalFormat("#.00");
	  // String newtotal=df.format(total);
	 %>

<tr>
	<td> <%=count %> <% count++; %> </td>
	<td> Total </td>
	<td>  </td>
	<td align="right"> <%=total %></td>
	<td align="right"> <%=totalob %></td>
	<td align="right"> <%=totalclosing %> </td>
</tr>

</tbody>    
</table>
<br><br> 
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

    <%
        if (exportToExcel == null) {
    %>
    <a href="TrialBalance.jsp?exportToExcel=YES">Export to Excel</a>
    <%
        }
    %>

</div>

<%--
<script>
$('tr').each(function () {
	  a = $(this).children('td:eq(3)').text();
	  if (a == "0.0") {
	      $(this).addClass('hidden');
	  }
	})
</script>
--%>
<br>
<br>
From <input type="text" name="fromdate"/> To <input type="text" name="todate" /><input type="submit" value="Generate"/>

<center><input type="button" value="Print this page" onClick="window.print()"></center>

</form>

</body>
</html>